package com.nbgroup.dao.Identity;


import com.nbgroup.entity.Identity.Identity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

/**
 * Created by 顾志杰 on 2018/8/15.
 */
public interface IdentityDao extends JpaRepository<Identity, String>, JpaSpecificationExecutor<Identity>, PagingAndSortingRepository<Identity, String>  {
        Identity findByIdentityId(String id);


    /**
     * 根据性别查询人的数量
     *
     * @param sex 性别
     * @return 数量
     */
    Long countBySex(String sex);

    Long countBySexAndRecentDateBetween(String sex ,String startTime ,String endTime);


    /**
     * 根据年龄段查询人数
     * 如查询10至20岁的人数
     *
     * @param start 开始年龄
     * @param end   结束年龄
     * @return 人数
     */
    @Query(value = "SELECT count(*) FROM identity WHERE TIMESTAMPDIFF(YEAR, date_of_birth, curdate()) BETWEEN :start AND :end", nativeQuery = true)
    Long countByAge(@Param(value = "start") Integer start, @Param(value = "end") Integer end);

}
